sql语句执行过程

#sql语句执行过程

首先,了解mysql的几个概念

  • 连接器:管理连接和权限验证
  • 分析器:词法和语法的分析
  • 优化器:生成执行计划,选择索引
  • 执行器:操作存储引擎接口,并返回操作结果
  • 查询缓存:对查询过的结果进行缓存
  • 存储引擎:存储数据,提供读写接口

我们把以上功能进行划分,可以吧mysql分为系统层和存储层。
系统层包括连接器、分析器、优化器、执行器和存储缓存。mysql的大多数核心功能和内置函数都在这一层上,比如存储过程、触发器、视图等。
存储层的存储引擎负责数据管理。目前常用的是MyISAM、InnoDB、Memory等。

下面对以下的sql语句进行分析,看看它的具体操作流程是怎么样的。

select * from user where uid = 1;

连接器

首先,程序将连接到数据库,这时候连接器就会对连接请求进行验证,然后获取操作权限并管理连接。例如:

mysql -h 127.0.0.1 -P 3306 -u mysql -p

当你成功建立连接后,这条连接就会保持住,直到你主动断开链接,或者在长时间没有动作后系统自动断开。这个时间是由wait_timeout参数控制的,默认8小时。
你可以通过 show processlist 命令产看当前的链接状态。
建立连接通常会消耗较多的系统资源,所以在开发过程中需要尽量避免频繁建立连接。
但是如果全部使用长连接,会导致内存使用率上升。因为mysql执行时使用的内存在断开连接的时候才会释放,如果内存占满就会被系统强行终止。
目前有两个比较常用的解决方案:
1. 定时断开长连接,或者在执行大量数据查询后断开连接。
2. 如果版本>5.7,可以使用`mysql_rest_connection来初始化连接。

查询缓存

在建立连接之后,mysql获取到查询请求,会先到查询缓存中检查之前有没有执行过这条命令。
之前执行过的语句及其结果会以key-value的形式缓存到内存中。key是查询语句,value是查询结果。
如果mysql找到了对应的缓存,则会将value直接返回给客户端。如果没有找到,就会继续执行后面的阶段。
但是一般情况下,不建议开启查询缓存。只要一张表的内容产生更新,这张表的缓存都会被清空。除非是一张比较大静态表,才适合使用查询缓存。
通过设置参数query_cache_typeDEMAND来设定默认不使用查询缓存。这是如果你想使用的话可以显式的指定。

select SQL_CACHE * from user where uid = 1;

另外,mysql8.0版本直接把查询缓存这个功能模块删掉了,也就是说未来mysql将不在支持使用查询缓存这个功能。

分析器

mysql开始对你的语句进行“词法分析”,从你输出的select关键字识别出这是一个查询语句;把user识别为表名;把uid识别为列名。
做完“词法分析”后,再进行“语法分析”,判断这个sql语句是否符合MYSQL的语法规范。如果语句不对则抛出异常并结束操作。

优化器

经过分析器后,mysql系统就知道你要做什么了。接下来在执行实际的查询之前,需要对执行的操作进行选择。
优化器是在表里有多个索引的时候,选择使用哪个索引;在一个语句有多表关联的时候,决定表的连接顺序。优化器会根据已有的索引、表的大小、字段长度等信息决定一个最优的查询方案。

执行器

当mysql系统确定好执行方案后,通过执行器调用存储引擎的操作接口开始进行数据的查询操作。
在开始执行的时候,回对当前的用户是否具备执行权限进行判断。如果没有就会返回权限提示。如果正常则会继续执行。
打开表的时候,执行器就会根据表的引擎定义,去使用对应的引擎接口。
假设 user 表的 uid 字段没有索引,mysql系统调用InnoDB引擎接口获取表的第一行,判断uid是否等于1,如果不是则跳过,如果是则将结果存入结果集中。然后继续调用引擎接口取下一行,重复刚刚的判断直到表的最后一行。最后执行器将结果集返回给客户端。
假设 uid 字段有索引,mysql系统将通过索引直接定位到uid=1的行,将结果返回出来。

centos安装xdebug

centos 7

  1. 安装 pecl
    1.1 安装所需php扩展

$ rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
$ rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm

1.2 下载并安装PECL
//php版本 > 7

$ wget http://pear.php.net/go-pear.phar
$ php go-pear.phar

//php版本 < 7

$ yum install php-pear

1.3 把pecl添加到 PATH

$ cp /root/pear/bin/pecl /usr/bin

  1. 安装 xdebug
    2.1 解禁popen()函数
    在php.ini 找到 disable_functions = xxx, 删除popen并重启php-fpm

2.2 安装xdebug

$ pecl install xdebug

2.3 添加xdebug扩展到php.ini 并重启php-fpm
//具体拓展文件路径请参考安装完成后的说明

zend_extension=/usr/local/php/lib/php/extensions/no-debug-non-zts-20190902/xdebug.so

数据表的设计要点

  1. 数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询效率。

  2. 能够用数字类型的字段尽量选择数字类型而不用字符串类型的(电话号码),这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

  3. 对于不可变字符类型char和可变字符类型varchar 都是8000字节,char查询快,但是耗存储空间,varchar查询相对慢一些但是节省存储空间。在设计字段的时候可以灵活选择,例如用户名、密码等长度变化不大的字段可以选择CHAR,对于评论等长度变化大的字段可以选择VARCHAR。

  4. 字段的长度在最大限度的满足可能的需要的前提下,应该尽可能的设得短一些,这样可以提高查询的效率,而且在建立索引的时候也可以减少资源的消耗。

限时秒杀系统设计要点

需求定义

在一定时间内,对一定数量的库存商品进行出售。

场景分析

  1. 在活动期间,对服务器的资源负载很高,如静态资源,商品信息,这些可以事先进行缓存,降低服务器负载。
  2. 防止用户进行刷单, 对uid或ip的访问频次进行限制,或者增加验证机制,如图形验证码,短信验证码等。
  3. 预备好中转界面,如稍后界面、排队中界面,避免直接卡页面对用户产生负面影响。

设计思路

前端

  • 页面静态化
  • 禁止重复提交

后端

  • 内存缓存
  • 负载均衡
  • 使用队列处理峰值请求
  • 异步处理
  • 结束后通知前端关闭入口,避免后续流量冲击